package com.xmy.cultivate.mapper;

import com.xmy.cultivate.entity.RecordCourse;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.xmy.cultivate.entity.views.EnrollGrantCount;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;

/**
 * <p>
 * 记上课 Mapper 接口
 * </p>
 *
 * @author hpiggy
 * @since 2024-03-20
 */
public interface RecordCourseMapper extends BaseMapper<RecordCourse> {

    @Select("select IFNULL(MIN(lesson_num),0) as lesson_num_min from record_course where year_part =${yearPart} and `quarter` =${quarterNum} and student_id = ${studentId} and subjects_id =${subjectsId} and lesson_type = ${lessonType} and deleted = 0 and course_type in(1,4)")
    public abstract Integer getMinLessonNum(@Param("schoolId") Long shoolId, @Param("studentId") Long studentId, @Param("subjectsId") Long subjectsId, @Param("yearPart") Integer yearPart, @Param("quarterNum") Integer quarterNum, @Param("lessonType") Integer lessonType);

    @Select("select count(id) as count from record_course where student_id =${studentId} and subjects_id =${subjectsId} and year_part =${yearPart}  and `quarter` = ${quarterNum} and deleted = 0 and ((course_type = 1 and lesson_num =${lessonNum}) or (course_type = 4 and lesson_num =${lessonNum}))")
    public abstract Integer getStudentUpClassCountForLessonNum(@Param("schoolId") Long shoolId, @Param("studentId") Long studentId, @Param("subjectsId") Long subjectsId, @Param("yearPart") Integer yearPart, @Param("quarterNum") Integer quarterNum, @Param("lessonType") Integer lessonType, @Param("lessonNum") Integer lessonNum);

    @Update("update record_course set deleted= ${deleted} where id = ${id} ")
    public Integer updateDeleted(@Param("id") Long id, @Param("deleted") Integer deleted);

    @Select({"<script>",
            "SELECT",
            "count(id) as count",
            "FROM",
            "record_course",
            "WHERE",
            "deleted = 0 ",
            "AND year_part = ${yearPart} ",
            "AND `quarter` = ${quarterNum} ",
            "AND lesson_type = ${lessonType} ",
            "AND `status` = 2 ",
            "AND student_id = ${studentId} ",
            "AND subjects_id = ${subjectsId} ",
            "AND is_repair &lt;&gt;1 ",
            "AND is_deduct_money = 0",
            "</script>"})
    public abstract Integer getLeaveCount(@Param("yearPart") Integer yearPart,
                                          @Param("quarterNum") Integer quarterNum,
                                          @Param("lessonType") Integer lessonType,
                                          @Param("studentId") Long studentId,
                                          @Param("subjectsId") Long subjectsId);

    @Select({"<script>",
            "SELECT",
                "* ",
            "FROM",
            "(",
            "SELECT",
                "t1.school_id,",
                "t1.student_id,",
                "t1.subjects_id,",
                "t1.year_part,",
                "t1.`quarter`,",
                "t1.`lesson_type`,",
                "t1.year_and_quarter,",
                "count( t1.id ) AS solidCount ",
            "FROM",
                "record_course AS t1",
            "INNER JOIN grade AS t2 ON t1.old_grade_id = t2.id ",
            "AND t2.is_makeup = 0 ",
            "WHERE",
                "t1.deleted = 0 ",
                "AND t1.year_part = ${yearPart} ",
                "AND t1.`quarter` = ${quarterNum} ",
                "<when test='studentId!=null and studentId!=&apos;&apos;'>",
                    "and t1.`student_id` = ${studentId}",
                "</when>",
                "AND t1.lesson_type = ${lessonType} ",
                "AND t1.buckle_class_house &gt; 0 ",
                "and (t1.`status`= 1 or (t1.`status` &lt;&gt; 1 and deduct_type = 4))",
            "GROUP BY",
                "t1.student_id,",
                "t1.subjects_id ",
            ") AS t ",
            "WHERE",
                "solidCount &gt;= ${rCount}",
            "</script>"})
    public abstract List<RecordCourse> getRecordCourseCount(@Param("yearPart") Integer yearPart,
                                                            @Param("quarterNum") Integer quarterNum,
                                                            @Param("studentId") Long studentId,
                                                            @Param("lessonType") Integer lessonType,
                                                            @Param("rCount") Integer rCount);

    @Select("select count(id) as count from record_course where deleted=0 and year_part = ${yearPart} and `quarter` = ${quarterNum} and lesson_type = 1 and `status`=1 and student_id = ${studentId} and subjects_id = ${subjectsId} and is_deduct_house =1 and is_deduct_money =1")
    public abstract Integer getRecordCountArrive(@Param("yearPart") Integer yearPart,@Param("quarterNum") Integer quarterNum,@Param("studentId") Long studentId,@Param("subjectsId") Long subjectsId);


    /**
     * 获取提成发放校区使用
     * @param yearPart
     * @param quarterNum
     * @param subjectsId
     * @param studentId
     * @param lessonType
     * @return
     */
    @Select({"<script>",
            "SELECT",
            "id,",
            "school_id,",
            "count( id ) AS count ",
            "FROM",
            "(",
            "SELECT",
            "* ",
            "FROM",
            "record_course ",
            "WHERE",
            "year_part = ${yearPart} ",
            "AND `quarter` = ${quarterNum} ",
            "AND subjects_id = ${subjectsId} ",
            "AND student_id = ${studentId} ",
            "AND lesson_type = ${lessonType} ",
            "AND deleted = 0 ",
            "AND `status` = 1 ",
            "ORDER BY",
            "created_at ASC ",
            "LIMIT 2 ",
            ") AS t ",
            "GROUP BY",
            "school_id",
            "</script>"})
    public abstract List<EnrollGrantCount> getEnrollGrantCount(@Param("yearPart") Integer yearPart,
                                                               @Param("quarterNum") Integer quarterNum,
                                                               @Param("subjectsId") Long subjectsId,
                                                               @Param("studentId") Long studentId,
                                                               @Param("lessonType") Integer lessonType
    );

    @Select({"<script>",
            "select MAX(lesson_num) as lesson_num from record_course where year_part = ${yearPart} and quarter = ${quarterNum} and lesson_type = ${lessonType} and deleted = 0",
            "</script>"})
    public abstract Integer getMaxLessonNumForInClass(@Param("yearPart") Integer yearPart,
                                                      @Param("quarterNum") Integer quarterNum,
                                                      @Param("lessonType") Integer lessonType);
}
